MySQL doesn’t support the INTERSECT and MINUS set operators. The INTERSECT operator takes the results of two queries and returns only rows that appear in both result sets. The MINUS operator takes the distinct rows of one query and returns the rows that do not appear in a second result set. We can rewrite these queries by using JOIN operator:
Sample query with the MINUS operator:
1 2 3 |
SELECT x, y FROM table_a MINUS SELECT x, y FROM table_b; |
In MySQL:
1 2 3 4 |
SELECT a.x, a.y FROM table_a a LEFT JOIN table_b b ON a.x = b.x AND a.y = b.y WHERE b.x IS NULL; |
Sample query with the INTERSECT operator:
1 2 3 |
SELECT x, y FROM table_a INTERSECT SELECT x, y FROM table_b; |
In MySQL:
1 2 3 |
SELECT a.x, a.y FROM table_a a JOIN table_b b ON a.x = b.x AND a.y = b.y; |
8
Saulo Fonseca
Gokhan Atil
Sandhikshan
Gokhan Atil
sujit gupta
Pavel
Gokhan Atil
chandu
surendra
Gokhan Atil
rahul DM khakse
raul
Gokhan Atil
sahil jain
Amis
Amis
Guillermo Malagón
Guillermo Malagón
Devasish
udhayakumar
Jay
Lefteris
Dav
Lewis Cowles (@LewisCowles1)
Oracle Training in Chennai
Pingback: How to sum $row
JAM
Happy
vs
Gokhan Atil
Pingback: SQL Reminder / Main Knowledge – Notes
claudio peña
Pranita
Volkan Düvencioğlu
Salvador Zapata
Diksha
anamadhey